package com.util;

import java.awt.image.BufferedImage;
import java.io.ByteArrayInputStream;
import java.io.ByteArrayOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.sql.Blob;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.HashMap;

import javax.imageio.ImageIO;

public class MySQLManager implements StorageManager {

	private String username;
	private Connection con;
	private boolean loggedIn;

	private final String ADD_SHELF = "INSERT INTO " + Constant.SHELF_TABLE
			+ " VALUES(?,?,?,?,?,?);";
	private final String DELETE_LIB = "DELETE FROM " + Constant.LIBRARY_TABLE
			+ " WHERE Username = ? AND LibName = ?;";
	private final String INSERT_DESIGN = "INSERT INTO "
			+ Constant.LIBRARY_TABLE
			+ "(LibId, Username, LibName, LibImage) VALUES(?, ?, ?, ?);";
	private final String SELECT_ALL_LIB = "SELECT LibID, LibName, LibImage FROM "
			+ Constant.LIBRARY_TABLE + " WHERE Username = ?;";
	private final String SELECT_ALL_SH = "SELECT xCord, yCord, RangeBeg, RangeEnd FROM "
			+ Constant.SHELF_TABLE + " WHERE LibID = ?;";
	private final String REMOVE_SHELF = "DELETE FROM " + Constant.SHELF_TABLE
			+ " WHERE LibID = ? AND xCord = ? AND yCord = ?";
	private final String UPDATE_SHELF = "UPDATE " + Constant.SHELF_TABLE
			+ " SET RangeBeg = ?, RangeEnd = ? WHERE xCord = ? AND yCord = ?";

	private PreparedStatement addShelf;
	private PreparedStatement deleteLib;
	private PreparedStatement insertDesign;
	private PreparedStatement selectLibs;
	private PreparedStatement selectShelves;
	private PreparedStatement removeShelf;
	private PreparedStatement updateShelf;

	public static final String CONNECTOR = "jdbc:mysql://";

	public MySQLManager(String url, String username, String password) {
		this.username = username;

		try {
			Class.forName("com.mysql.jdbc.Driver");
			con = DriverManager.getConnection(url, username, password);
			loggedIn = true;

			addShelf = con.prepareStatement(ADD_SHELF);
			deleteLib = con.prepareStatement(DELETE_LIB);
			insertDesign = con.prepareStatement(INSERT_DESIGN);
			selectLibs = con.prepareStatement(SELECT_ALL_LIB);
			selectShelves = con.prepareStatement(SELECT_ALL_SH);
			removeShelf = con.prepareStatement(REMOVE_SHELF);
			updateShelf = con.prepareStatement(UPDATE_SHELF);
		} catch (SQLException e) {
			loggedIn = false;
		} catch (ClassNotFoundException e) {
			loggedIn = false;
		}
	}

	@Override
	public boolean addShelf(Shelf s, String name) {
		try {
			Statement maxShelfId = con.createStatement();
			ResultSet max = maxShelfId
					.executeQuery("SELECT Max(ShelfNum) FROM Shelf;");

			Statement id = con.createStatement();
			ResultSet rs = id
					.executeQuery("SELECT LibID FROM LIBRARY WHERE LibName = '"
							+ name + "';");

			int num;
			if (max != null && max.next()) {
				num = max.getInt(1);
			} else
				num = 0;

			int libId;
			if (rs != null && rs.next()) {
				libId = rs.getInt(1);
			} else
				return false;

			addShelf.setInt(1, libId);
			addShelf.setInt(2, num + 1);
			addShelf.setString(3, s.getBegId());
			addShelf.setString(4, s.getEndId());
			addShelf.setDouble(5, s.getX());
			addShelf.setDouble(6, s.getY());
			addShelf.execute();
			return true;
		} catch (SQLException e) {
			e.printStackTrace();
			return false;
		}
	}

	@Override
	public boolean deleteDesign(String name) {
		try {
			Statement id = con.createStatement();
			ResultSet rs = id
					.executeQuery("SELECT LibID FROM LIBRARY WHERE LibName = '"
							+ name + "';");

			int num;
			if (rs != null && rs.next()) {
				num = rs.getInt(1);
			} else
				return false;

			deleteLib.setString(1, username);
			deleteLib.setString(2, name);
			deleteLib.execute();

			Statement update = con.createStatement();
			update.execute("UPDATE Library SET LibID = LibID - 1 WHERE LibID > "
					+ num);
			return true;
		} catch (SQLException e) {
			return false;
		}
	}

	@Override
	public boolean insertDesign(Design d) {
		ByteArrayOutputStream baos = new ByteArrayOutputStream();
		try {
			ImageIO.write(d.lib.getImg(), "png", baos);
		} catch (IOException e1) {
			e1.printStackTrace();
			return false;
		}
		InputStream fis = new ByteArrayInputStream(baos.toByteArray());

		try {
			Statement s = con.createStatement();
			ResultSet max = s.executeQuery("SELECT Max(LibID) FROM Library;");
			int num;
			if (max != null && max.next()) {
				num = max.getInt(1);
			} else
				num = 0;

			insertDesign.setInt(1, num + 1);
			insertDesign.setString(2, username);
			insertDesign.setString(3, d.getName());
			insertDesign.setBinaryStream(4, fis);
			insertDesign.execute();
			return true;
		} catch (SQLException e) {
			e.printStackTrace();
			return false;
		}
	}

	public boolean isConnected() {
		return loggedIn;
	}

	@Override
	public void populateDesigns(HashMap<String, Design> designs) {

		try {
			selectLibs.setString(1, username);

			ResultSet getLibs = selectLibs.executeQuery();
			while (getLibs != null && getLibs.next()) {
				int id = getLibs.getInt(1);
				String name = getLibs.getString(2);
				Blob blob = getLibs.getBlob(3);
				byte[] data = null;

				if (blob != null) {
					int blobLength = (int) blob.length();
					data = blob.getBytes((long) 1, blobLength);
					blob.free();
					BufferedImage img = ImageIO.read(new ByteArrayInputStream(
							data));
					designs.put(name, new Design(name, img));
				}

				selectShelves.setInt(1, id);
				ResultSet getShelves = selectShelves.executeQuery();

				while (getShelves != null && getShelves.next()) {
					float x = getShelves.getFloat(1);
					float y = getShelves.getFloat(2);
					String begId = getShelves.getString(3);
					String endId = getShelves.getString(4);

					Design d = designs.get(name);
					d.lib.addShelf(new Shelf(x, y, begId, endId));
				}
				getShelves.close();
			}
			getLibs.close();
		} catch (Exception e) {
			e.printStackTrace();
		}
	}

	@Override
	public boolean removeShelf(double x, double y, String name) {

		try {
			Statement id = con.createStatement();
			ResultSet rs = id
					.executeQuery("SELECT LibID FROM LIBRARY WHERE LibName = '"
							+ name + "';");

			int num;
			if (rs != null && rs.next()) {
				num = rs.getInt(1);
			} else
				return false;

			removeShelf.setInt(1, num);
			removeShelf.setDouble(2, x);
			removeShelf.setDouble(3, y);
			removeShelf.execute();

			Statement update = con.createStatement();
			update.execute("UPDATE Shelf SET ShelfNum = ShelfNum - 1 WHERE ShelfNum > "
					+ num);
			return true;
		} catch (SQLException e) {
			return false;
		}
	}

	@Override
	public boolean updateDesign(Design workingDesign) {
		String name = workingDesign.getName();
		ArrayList<Shelf> shelves = workingDesign.lib.getShelves();
		// TO FINISH
		return false;
	}

	@Override
	public boolean updateShelf(Shelf s) {
		try {
			updateShelf.setString(1, s.getBegId());
			updateShelf.setString(2, s.getEndId());
			updateShelf.setDouble(3, s.getX());
			updateShelf.setDouble(4, s.getY());
			updateShelf.execute();
			return true;
		} catch (SQLException e) {
			e.printStackTrace();
			return false;
		}
	}
}
